package com.common.utils;

import java.io.IOException;
import java.net.InetSocketAddress;
import java.net.Socket;
import java.net.SocketAddress;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PropertiesLoaderUtils;

import com.alibaba.fastjson.JSONObject;
import com.business.base.Pager;

/**
 * 关系型数据操作相关类封装
 * 
 * @author lijinfeng
 * @date 2015-09-28
 * @version 1.0
 */
public class DataBaseHelper {
	/**
	 * 日志记录
	 */
	private static Logger logger = Logger.getLogger(DataBaseHelper.class);
	/**
	 * 数据库连接
	 */
	private static Connection connection = null;

	private DataBaseHelper() {
	}
	/**
	 * return ResultSet List
	 */
	public List<Object> getResultList(ResultSet res) {
		List<Object> list = new ArrayList<Object>();
		try {
			ResultSetMetaData md = res.getMetaData();
			int columnCount = md.getColumnCount();
			while (res.next()) {
				Map<Object, Object> rowData = new HashMap<Object, Object>();
				for (int i = 1; i <= columnCount; i++)
					rowData.put(md.getColumnName(i), res.getObject(i));
				list.add(rowData);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * return connection 
	 * Oracle:"oracle.jdbc.dirver.OracleDriver",
	 * "jdbc:oracle:thin:@localhost:1521:DBName"
	 * Sql:"com.microsoft.jdbc.sqlserver.SQLServerDriver"
	 * ,"jdbc:sqlserver://localhost:1433;databaseName=数据库名"
	 * MySQL:"com.mysql.jdbc.Driver","jdbc:mysql://localhost:3306/dv_db_oa"
	 * Sybase:"base.jdbc.SybDriver","jdbc:sybase:Tds:localhost:5007/erp "
	 * DB2:"com.ibm.db2.app.DB2Driver","jdbc:db2//localhost:5000/testDB"
	 * Hsql:"org.hsqldb.jdbcDriver","jdbc:hsqldb:hsql://localhost:9902"
	 * Informix:"com.informix.jdbc.IfxDriver",
	 * "jdbc:informixsqli://123.45.67.89:1533/myDB:INFORMIXSERVER=myserver"
	 * PostgreSQL:"org.postgresql.Driver","jdbc:postgresql://localhost/testDB"
	 * HIVE:"org.apache.hadoop.hive.jdbc.HiveDriver","jdbc:hive://ip:50000/default"
	 */
	@SuppressWarnings("resource")
	public static Connection getConnection(String driverName, String dbUrl,
			String username, String password) {
		try {
			//数据库连接之前先判断网络是否连通
			String ipAddr=null;
			int port=3306;
			if(driverName.endsWith("com.mysql.jdbc.Driver")){
				String[] strs=dbUrl.substring(13).split(":");
				if(strs.length>1){//带端口号的url
					ipAddr=strs[0];
					port=Integer.parseInt(strs[1].split("/")[0]);
				}else{//不带端口号的url
					ipAddr=dbUrl.substring(13).split("/")[0];
					port=3306;
				}
			}else if(driverName.equals("oracle.jdbc.driver.OracleDriver")){
				String[] strs=dbUrl.substring(18).split(":");
				if(strs.length>1){//带端口号的url
					ipAddr=strs[0];
					port=Integer.parseInt(strs[1].split("/")[0]);
				}else{//不带端口号的url
					ipAddr=dbUrl.substring(18).split("/")[0];
					port=1521;
				}
			}
			Socket mSocket = new Socket();
			SocketAddress socketAddress = new InetSocketAddress(ipAddr,port);
			mSocket.connect(socketAddress,5000);
			Class.forName("oracle.jdbc.driver.OracleDriver");
			DriverManager.setLoginTimeout(10);
			// Properties properties=new Properties();
			// properties.put("username", username);
			// properties.put("password", password);
			// connection=DriverManager.getConnection(dbUrl,properties);
			connection = DriverManager.getConnection(dbUrl, username, password);
		} catch (ClassNotFoundException e) {
			//e.printStackTrace();
			logger.error(e.getMessage());
		} catch (SQLException e) {
			//e.printStackTrace();
			logger.error(e.getMessage());
		} catch (IOException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage());
		}
		return connection;
	}
	public final static String getDriverName(){
		//驱动类型初始化
		String driverName = null;
		//orcl驱动
		String orclName = "oracle";
		//SqlServer驱动
		String sqlServerName = "sqlserver";
		//mysql驱动
		String mysqlName = "mysql";
		//读取配置文件
		Resource resource = new ClassPathResource("application.properties");
		String propertyKey = "spring.datasource.driver-class-name";
		Properties props=null;
		try {
			props = PropertiesLoaderUtils.loadProperties(resource);
			String driverFullName = props.getProperty(propertyKey);
			if(driverFullName!=null){
				if(driverFullName.toLowerCase().contains(orclName)){
					driverName = orclName;
				}else if(driverFullName.toLowerCase().contains(sqlServerName)){
					driverName = sqlServerName;
				}else{
					driverName = mysqlName;
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return driverName;
	}
	/**
	 * return ResultSet TABLE_CAT String => 表类别（可为 null） TABLE_SCHEM String =>
	 * 表模式（可为 null） TABLE_NAME String => 表名称 TABLE_TYPE String => 表类型。 REMARKS
	 * String => 表的解释性注释 TYPE_CAT String => 类型的类别（可为 null） TYPE_SCHEM String =>
	 * 类型模式（可为 null） TYPE_NAME String => 类型名称（可为 null） SELF_REFERENCING_COL_NAME
	 * String => 有类型表的指定 "identifier" 列的名称（ 可为 null） REF_GENERATION String
	 * SELF_REFERENCING_COL_NAME String => name of the designated "identifier"
	 * column of a typed table (may be null) REF_GENERATION String => specifies
	 * how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM",
	 * "USER", "DERIVED". (may be null)
	 */
	public static List<Object> getAllTablesInfo(Connection conn) {
		List<Object> list = new ArrayList<Object>();
		ResultSet resTable = null;
		try {
			DatabaseMetaData dbMetaData = conn.getMetaData();
			resTable = dbMetaData.getTables(conn.getCatalog(), null, "%", null);
			ResultSetMetaData rMetaData = resTable.getMetaData();
			int table_count = rMetaData.getColumnCount();
			while (resTable.next()) {
				Map<Object, Object> map = new HashMap<Object, Object>();
				for (int i = 1; i < table_count; i++)
					map.put(rMetaData.getColumnName(i), resTable.getString(i));
				list.add(map);
			}
		} catch (Exception e) {
			//e.printStackTrace();
			logger.error(e.getMessage());
		}
		return list;
	}

	/**
	 * return all tables name
	 */
	public static List<Object> getAllTablesNmae(Connection conn) {
		List<Object> list = new ArrayList<Object>();
		ResultSet resTable = null;
		try {
			DatabaseMetaData dbMetaData = conn.getMetaData();
			resTable = dbMetaData.getTables(conn.getCatalog(), null, "%", null);
			while (resTable.next()) {
				list.add(resTable.getString(3));
				/**
				 * 元数据的列分别为：TABLE_CAT、TABLE_SCHEM、 TABLE_NAME、TABLE_TYPE、REMARKS
				 */
			}
		} catch (Exception e) {
			//e.printStackTrace();
			logger.error(e.getMessage());
		}
		return list;
	}
	/**
	 * return all column name
	 */
	public static String[] getColNames(ResultSet res) throws SQLException {
		ResultSetMetaData rsmd = res.getMetaData();
		int count = rsmd.getColumnCount();
		String[] colNames = new String[count];
		for (int i = 1; i <= count; i++) {
			colNames[i - 1] = getUpper(rsmd.getColumnLabel(i));
		}
		return colNames;
	}
	/**
	 * 获取表中所有的列信息
	 * @param conn
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String,Object>> getColumns(Connection conn,String type,String user,String tableName) throws Exception{
		List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
		ResultSet rs=null;
		ResultSet r=null;
		String schema = null;
		try{
			DatabaseMetaData dbmd=conn.getMetaData();
			rs=dbmd.getColumns(null, schema, tableName, "%");
			r=dbmd.getPrimaryKeys(null, schema,tableName);
			String primary_name="";
			while (r.next()){
				primary_name=primary_name+(String) r.getObject("COLUMN_NAME")+"-";
			}
			while (rs.next()) {
				Map<String,Object> rowData = new HashMap<String,Object>(); 
				String column_name=(String) rs.getObject("COLUMN_NAME");
				if(null!=primary_name&&primary_name.equals(column_name)){
					rowData.put("IS_PK_FIELD","1");   
				}else{
					rowData.put("IS_PK_FIELD","0");    
				}
				String is_null=(String) rs.getObject("IS_NULLABLE");
				if("NO".equals(is_null)){
					rowData.put("IS_NULL_FIELD","1");
				}else{
					rowData.put("IS_NULL_FIELD","0");
				}
	        	rowData.put("LENGTH",rs.getObject("COLUMN_SIZE"));   
	        	rowData.put("FIELD_TYPE",rs.getObject("TYPE_NAME"));
	        	rowData.put("NAME_TABLE",column_name);
	        	list.add(rowData);
		   }
		   if(rs!=null){
			   rs.close();
		   }
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			if(r!=null){
				r.close();
			}
			try{
				if(conn!=null){
					conn.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		return list;
	}
	/**
	 * return 首字母大写字符串
	 */
	private static String getUpper(String column) {
		return column.substring(0, 1).toUpperCase() + column.substring(1);
	}

	/**
	 * return rows number
	 */
	public static int getRowssNumber(ResultSet res) {
		int rows = 0;
		try {
			res.last();
			rows = res.getRow();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rows;
	}
	/**
	 * 关闭链接
	 * 
	 * @param rs
	 * @param st
	 * @param ct
	 */
	public static void close(ResultSet res, Statement state,
			Connection connection) {
		try {
			if (res != null)
				res.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (state != null)
					state.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if (connection != null) {
					try {
						connection.close();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		}
	}

	/**
	 * 
	 * @param con
	 * @param sql
	 * @param paras
	 * @return
	 * @throws SQLException
	 */
	public static ResultSet query(Connection con, String sql, Object... paras)
			throws SQLException {
		ResultSet rs = null;
		PreparedStatement pst = null;
		//oracle默认ResultSet不支持结果集cursor的回滚
		pst = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
		if (paras != null) {
			for (int i = 0; i < paras.length; i++) {
				pst.setObject(i + 1, paras[i]);
			}
		}
		rs = pst.executeQuery();
		return rs;
	}

	/**
	 * 读取记录数
	 * 
	 * @param con
	 * @param sql
	 * @param objects
	 * @return
	 * @throws SQLException
	 */
	public static long getTotalInt(Connection con, String sql, Object... paras)
			throws SQLException {
		ResultSet rs = query(con, sql, paras);
		rs.last();
		return rs.getRow();
	}
	public static String replaceStr(String sql) throws Exception{
		if(StringUtils.isNotBlank(sql)){
			if(sql.indexOf("\\t")!=-1){
				sql=sql.replaceAll("\\\\t", "");
			}
			if(sql.indexOf("\\r")!=-1){
				sql=sql.replaceAll("\\\\r", " ");
			}
			if(sql.indexOf("\\n")!=-1){
				sql=sql.replaceAll("\\\\n", " ");
			}
		}
		return sql;
	}
	
	/**
     * 读取最大行
     * @param conn
     * @param sql
     * @param paras
     * @return
     * @throws Exception 
     */
    private static long getTotalRows(Connection conn,String sql,Object... paras) throws Exception{
    	ResultSet rs  = null;
    	PreparedStatement pst = null;
    	try{
    		pst = conn.prepareStatement(sql);
    		if(null!=paras&&paras.length>0){
    			for (int i=0; i<paras.length; i++) {
        			pst.setObject(i + 1, paras[i]);
        		}
    		}
    		rs = pst.executeQuery();
    		if(rs.next()){
    			return rs.getLong(1);
    		}
    	}catch(Exception e){
    		//e.printStackTrace();
			throw e;
    	}finally{
    		if(rs!=null){
    			rs.close();
    		}
    		if(null!=pst){
    			pst.close();
    		}
    	}
    	return 0;
    }
   
	 /**
     * 数据库分页查询
     * @param conn
     * @param pageNumber
     * @param pageSize
     * @param select
     * @param sqlExceptSelect
     * @param paras
     * @return
     * @throws SQLException
     */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static Pager paginate(Connection conn, int pageNumber, int pageSize, String select, String sqlExceptSelect, Object... paras) throws Exception{
		Pager<Map<String,Object>> page = new Pager();
		page.setPageNum(pageNumber);
		page.setPageSize(pageSize);
		if (pageNumber < 1 || pageSize < 1){
			page.setTotal(0);
			return page;
		}
		long totalRow = 0;
		int totalPage = 0;
		String sqls = "select count(1) " + replaceFormatSqlOrderBy(sqlExceptSelect);
		totalRow = getTotalRows(conn,sqls,paras);//this.readSqlSessionTemplate.selectOne(statement, parameter)
		if(totalRow==0){
			page.setTotal(0);
			return page;
		}
		totalPage = (int) (totalRow / pageSize);
		if (totalRow % pageSize != 0) {
			totalPage++;
		}
		JSONObject json =  getDbPaginate(conn, pageNumber, pageSize, select, sqlExceptSelect);
		String sql = json.getString("sql");
		List<Map<String,Object>> list = find(conn, sql, paras);
		page.setPages(totalPage);
		page.setTotal(totalRow);
		page.setList(list);
		return page;
	}
	/**
	 * MySQL数据库分页
	 * @param sql
	 * @param pageNumber
	 * @param pageSize
	 * @param select
	 * @param sqlExceptSelect
	 */
	private static void forMySQLPaginate(StringBuilder sql, int pageNumber, int pageSize, String select, String sqlExceptSelect) {
		int offset = pageSize * (pageNumber - 1);
		sql.append(select).append(" ");
		sql.append(sqlExceptSelect);
		sql.append(" limit ").append(offset).append(", ").append(pageSize);	// limit can use one or two '?' to pass paras
	}
	/**
	 * Oracle数据库分页
	 * @param sql
	 * @param pageNumber
	 * @param pageSize
	 * @param select
	 * @param sqlExceptSelect
	 */
	private static void forOraclePaginate(StringBuilder sql, int pageNumber, int pageSize, String select, String sqlExceptSelect) {
		int start = (pageNumber - 1) * pageSize + 1;
		int end = pageNumber * pageSize;
		sql.append("select * from ( select row_.*, rownum rownum_ from (  ");
		sql.append(select).append(" ").append(sqlExceptSelect);
		sql.append(" ) row_ where rownum <= ").append(end).append(") table_alias");
		sql.append(" where table_alias.rownum_ >= ").append(start);
	}
	/**
	 * 替换SQL OrderBy
	 * @param sql
	 * @return
	 */
	private static String replaceFormatSqlOrderBy(String sql) {
		sql = sql.replaceAll("(\\s)+", " ");
		int index = sql.toLowerCase().lastIndexOf("order by");
		if (index > sql.toLowerCase().lastIndexOf(")")) {
			String sql1 = sql.substring(0, index);
			String sql2 = sql.substring(index);
			sql2 = sql2.replaceAll("[oO][rR][dD][eE][rR] [bB][yY] [\u4e00-\u9fa5a-zA-Z0-9_.]+((\\s)+(([dD][eE][sS][cC])|([aA][sS][cC])))?(( )*,( )*[\u4e00-\u9fa5a-zA-Z0-9_.]+(( )+(([dD][eE][sS][cC])|([aA][sS][cC])))?)*", "");
			return sql1 + sql2;
		}
		return sql;
	}
	/**
	 * 返回数据库分页
	 * @param con
	 * @return
	 * @throws SQLException 
	 */
	public static JSONObject getDbPaginate(Connection con,int pageNumber,int pageSize,String select,String sqlExceptSelect) throws SQLException{
		String dataType = "Oracle";
		StringBuilder sqlBuilder = new StringBuilder(255);
		DatabaseMetaData metaData = con.getMetaData();
		if(metaData!=null){
			String name = metaData.getDatabaseProductName();
			if(StringUtils.isNotBlank(name)){
				if(name.startsWith("Oracle")){
					dataType = "Oracle";
					forOraclePaginate(sqlBuilder, pageNumber, pageSize, select, sqlExceptSelect);
				}else if(name.startsWith("MySQL")){
					dataType = "MySQL";
					forMySQLPaginate(sqlBuilder, pageNumber, pageSize, select, sqlExceptSelect);
				}
			}else{
				 logger.error("查找不到数据类型.");
			}
		 }
		JSONObject json = new JSONObject();
		json.put("type", dataType);
		json.put("sql", sqlBuilder.toString());
		return json;
	}
    /**
     * 构建数据库
     * @param conn
     * @param sql
     * @param paras
     * @return
     * @throws SQLException
     */
    private static List<Map<String,Object>> find(Connection conn, String sql, Object... paras) throws Exception{
    	List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
    	ResultSet rs  = null;
    	PreparedStatement pst = null;
    	try{
    		pst = conn.prepareStatement(sql);
    		for (int i=0; i<paras.length; i++) {
    			pst.setObject(i + 1, paras[i]);
    		}
    		rs = pst.executeQuery();
    		ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount(); 
            Map<String,Object> rowData = new HashMap<String,Object>();   
            while (rs.next()) {   
              rowData = new HashMap<String,Object>(columnCount);   
              for (int i = 1; i <= columnCount; i++) {   
            	rowData.put(md.getColumnLabel(i), rs.getObject(i));   
              }   
              result.add(rowData);   
            }
    	}catch(Exception e){
    		throw e;
    	}finally{
    		try {
	    		if(rs!=null){
					rs.close();
	    		}
	    		if(pst!=null){
	    			pst.close();
	    		}
    		} catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
    	}
		return result;
	}
}
